package com.yao.netipro.test2;

import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;


/**
 * @Author: yaoym
 * @Description: 高德地图位置坐标实践，计算两点之间的直线距离，从而筛选最近的仓库
 * @Date: Created in 10:53 2019/7/1
 * @Modified By:
 */
public class Demo1 {

    public static void main(String[] args) {
        String url = "https://restapi.amap.com/v3/geocode/geo";
        Map<String, Object> map = new HashMap<>();
        map.put("key", "a649494a1f99d9173ff113719c421a45");
        map.put("address", "国人通信大厦");
        String result = HttpUtil.get(url, map);

        System.out.println(result);

        JSON json = JSONUtil.parse(result);

        String location = json.getByPath("geocodes[0].location", String.class);

        //经度
        String lng = location.split(",")[0];

        //纬度
        String lat = location.split(",")[1];

        System.out.println(lng);
        System.out.println(lat);

        try {
            DriverManager.registerDriver(new Driver());
            //jdbc链接mysql8.0必须加上参数serverTimezone=GMT%2B8
            String sqlurl = "jdbc:mysql://192.168.1.32:3306/neti?serverTimezone=GMT%2B8";
            String username="root";
            String password="123456";

            Connection connection = DriverManager.getConnection(sqlurl,username, password);

            String sql = "select t.id from\n" +
                    "(select w.id, st_distance(\n" +
                    "\tpoint(?, ?),\n" +
                    "\tpoint(w.lng, w.lat)\n" +
                    ")*111195/1000 as distance\n" +
                    "from t_warehouse w\n" +
                    "join t_warehouse_sku ws on ws.warehouse_id = w.id and ws.sku_id = ? and ws.num >= ? ) t  " +
                    "ORDER BY distance limit 1";
            PreparedStatement pst = connection.prepareStatement(sql);

            pst.setObject(1, lng);
            pst.setObject(2, lat);
            pst.setObject(3, 1);
            pst.setObject(4, 1);

            ResultSet resultSet = pst.executeQuery();

            while(resultSet.next()){
                int id = resultSet.getInt(1);
                System.out.println("id = "+id);
            }
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
